package com.tjtt.tdm.form.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.hibernate.Query;
import org.hibernate.Session;

import com.tjtt.common.utils.StringUtil;
import com.tjtt.tdm.base.BaseHibernateDao;
import com.tjtt.tdm.base.PageResult;
import com.tjtt.tdm.exception.ServiceException;
import com.tjtt.tdm.flow.bean.Flowtype;
import com.tjtt.tdm.flow.bean.TdmCustomNo;
import com.tjtt.tdm.form.bean.AutoFormsField;
import com.tjtt.tdm.form.dao.IAutoDao;
import com.tjtt.tdm.system.bean.LogInfo;
import com.tjtt.tdm.system.service.LogService;

public class AutoDaoImpl extends BaseHibernateDao implements IAutoDao {
	private LogService logService;
	
	public LogService getLogService() {
		return logService;
	}
	public void setLogService(LogService logService) {
		this.logService = logService;
	}
	public PageResult queryAutoFormData(Map filterMap,Map filterMapChild) {
		String formid=filterMap.get("formId").toString();
		String tableName = filterMap.get("tableName").toString();
		StringBuffer queryColumn = new StringBuffer();
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();
		String hql = "from AutoFormsField a where a.formsId="+filterMap.get("formId").toString() +" order by  showSeq,id asc ";
		List<AutoFormsField> list = getHibernateTemplate().find(hql);
		String cloumn = "";
		String leftSql = "";
		String type = "";
		int j = 1;
		for(int i = 0 ; i < list.size(); i++){
			AutoFormsField aff = list.get(i);
			if(!"ID".equals(aff.getFieldName())&&!"".equals(aff.getPageDisplayType())&&null!=aff.getPageDisplayType()){
				type =  aff.getPageDisplayType().toString();
			}
			if("11".equals(type)){//字段是选择人员
				String bm = "t"+j;
				queryColumn.append(bm+".username as "+aff.getFieldName()+",");
	//			queryColumn.append("t."+aff.getFieldName()+",");
				leftSql += " left join sys_users "+bm+" on "+bm+".userid in (t."+aff.getFieldName()+")";
				j++;
			}else if("10".equals(type)){//字段是部门
				String bm = "t"+j;
				queryColumn.append(bm+".org_name as "+aff.getFieldName()+",");
				leftSql += " left join sys_organization "+bm+" on t."+aff.getFieldName()+" = "+bm+".org_id";
				j++;
			}else if("13".equals(type)){//字段是上传文件
				String bm = "t"+j;
				queryColumn.append(bm+".FILE_NAME as "+aff.getFieldName()+",");
				//leftSql += " left join tdm_annex "+bm+" on t."+aff.getFieldName()+" = "+bm+".id";
				//leftSql += " left join tdm_attachment "+bm+" on t."+aff.getFieldName()+" = "+bm+".code_id";
				leftSql += " left join PTEM_ATTACHMENT "+bm+" on t."+aff.getFieldName()+" = "+bm+".code_id";
				j++;
			}
			else if(!"".equals(aff.getForeignTableName()) && aff.getForeignTableName() != null){
//				if(aff.getForeignDisCloumn().contains(",")){
//					String bm = "t"+j;
//					queryColumn.append(bm+"."+aff.getForeignDisCloumn().substring(0, aff.getForeignDisCloumn().indexOf(","))+" as "+aff.getFieldName()+",");
//					leftSql += " left join "+aff.getForeignTableName()+" "+bm+" on t."+aff.getFieldName()+"||'' = "+bm+"."+aff.getForeignCloumn()+"||''";
//					j++;
//				}else{
//					
//					String bm = "t"+j;
//					queryColumn.append(bm+"."+aff.getForeignDisCloumn()+" as "+aff.getFieldName()+",");
//					leftSql += " left join "+aff.getForeignTableName()+" "+bm+" on t."+aff.getFieldName()+"||'' = "+bm+"."+aff.getForeignCloumn()+"||''";
//					j++;
//				}
				queryColumn.append("t."+aff.getFieldName()+",");
			}else{
				queryColumn.append("t."+aff.getFieldName()+",");
			}
		}
		
		if(queryColumn.toString().length()>0){
			cloumn = queryColumn.toString().substring(0,queryColumn.toString().length()-1);
		}
		
		queryCountString.append(" select count(*) from  "+ tableName +" t " + leftSql );
		queryString.append(" select " + cloumn +" from "+ tableName +" t " + leftSql );
		
		queryCountString.append(" where 1=1 ");
		queryString.append(" where 1=1 ");
		if (filterMap!= null && !filterMap.isEmpty()) {
			
			if(null != filterMap.get("type") &&! "".equals(filterMap.get("type"))){
				queryString.append("and CATEGORY = '" + filterMap.get("type") + "'  ");
				queryCountString.append("and CATEGORY = '" + filterMap.get("type") + "'  ");
			}
			if(null != filterMap.get("formid") &&! "".equals(filterMap.get("formid"))){
				queryString.append("and auto_froms_id = '" + filterMap.get("formid") + "'  ");
				queryCountString.append("and auto_froms_id = '" + filterMap.get("formid") + "'  ");
			}
			if(null != filterMap.get("queryColumn") && !"".equals(filterMap.get("queryColumn"))){
				String queryColumns = String.valueOf(filterMap.get("queryColumn"));
				for(String column : queryColumns.split(",")){
					if(null != filterMap.get(column) &&! "".equals(filterMap.get(column))){
						String col = StringUtil.encodingUrl(filterMap.get(column).toString()).trim();
						if (col.indexOf(",")>0){
							String[] arrayValues = col.split(",");
							queryString.append(" and ( ");
							queryCountString.append(" and ( ");
							for (int i=0;i<arrayValues.length;i++){
								if (i==0){
									queryString.append(" t."+column+" like '%" + arrayValues[i] + "%' ");
									queryCountString.append(" t."+column+" like '%" + arrayValues[i] + "%' ");
								}
								else{
									queryString.append(" or t."+column+" like '%" + arrayValues[i] + "%' ");
									queryCountString.append(" or t."+column+" like '%" + arrayValues[i] + "%' ");
								}
							}
							queryString.append(" ) ");
							queryCountString.append(" ) ");
						}
						else{
							//查询数据类型			
							String hqlType="select fieldType from AutoFormsField t where t.fieldName='"+column+"'";
							List<String> strTypeL=getSession().createQuery(hqlType).list();
							String strType=strTypeL.get(0).toString();
							//
							if(strType.equals("DATE"))
							{
								queryString.append("and t."+column+"=to_date('" + col + "','YYYY/MM/DD')");
								queryCountString.append("and t."+column+"=to_date('" + col + "','YYYY/MM/DD')");
							
							}
							else
							{
								queryString.append("and t."+column+" like '%" + col + "%' ");
								queryCountString.append("and t."+column+" like '%" + col + "%' ");
							}	
						}		
					}
				}
			}
			//子表查询条件
			String formIdChild = (String) filterMapChild.get("formIdChild");
			String fieldChild =(String) filterMapChild.get("fieldChild");
			String fieldParent = (String) filterMapChild.get("fieldParent");
			if (filterMapChild!= null && !filterMapChild.isEmpty()) 
			{
				if(null != filterMapChild.get("queryColumnChild") && !"".equals(filterMapChild.get("queryColumnChild"))){
					String queryColumns = String.valueOf(filterMapChild.get("queryColumnChild"));
					queryString.append(" and t.");
					queryString.append(fieldParent);
					queryString.append(" in (select t2.");
					queryString.append(fieldChild);
					queryString.append(" from ");
					queryString.append(formIdChild);
					queryString.append(" t2 where 1=1 ");
					queryCountString.append(" and t.");
					queryCountString.append(fieldParent);
					queryCountString.append(" in (select t2.");
					queryCountString.append(fieldChild);
					queryCountString.append(" from ");
					queryCountString.append(formIdChild);
					queryCountString.append(" t2 where 1=1 ");
					//queryCountString.append(" and ( ");
					for(String column : queryColumns.split(",")){
						if(null != filterMapChild.get(column) &&! "".equals(filterMapChild.get(column))){
							String col = StringUtil.encodingUrl(filterMapChild.get(column).toString()).trim();
							if (col.indexOf(",")>0){
								String[] arrayValues = col.split(",");
								queryString.append(" and ");
								for (int i=0;i<arrayValues.length;i++){
									if (i==0){
										queryString.append(" t2."+column+" like '%" + arrayValues[i] + "%' ");
										queryCountString.append(" t2."+column+" like '%" + arrayValues[i] + "%' ");
									}
									else{
										queryString.append(" or t2."+column+" like '%" + arrayValues[i] + "%' ");
										queryCountString.append(" or t2."+column+" like '%" + arrayValues[i] + "%' ");
									}
								}							
							}
							else{
								//查询数据类型			
								String hqlType="select fieldType from AutoFormsField t where t.fieldName='"+column+"'";
								List<String> strTypeL=getSession().createQuery(hqlType).list();
								String strType=strTypeL.get(0).toString();
								//
								if(strType.equals("DATE"))
								{
									queryString.append("and t2."+column+"=to_date('" + col + "','YYYY/MM/DD')");
									queryCountString.append("and t2."+column+"=to_date('" + col + "','YYYY/MM/DD')");
								
								}
								else
								{
									queryString.append("and t2."+column+" like '%" + col + "%' ");
									queryCountString.append("and t2."+column+" like '%" + col + "%' ");
								}	
							}		
						}
					}
					queryString.append(")");
					queryCountString.append(")");
				}
			}
			if(null != filterMap.get("byTableColumn") &&! "".equals(filterMap.get("byTableColumn"))){
				queryString.append("and "+filterMap.get("byTableColumn")+" = '"+filterMap.get("byTableValue")+"' ");
				queryCountString.append("and "+filterMap.get("byTableColumn")+" = '"+filterMap.get("byTableValue")+"' ");
			}
			boolean sfpaixu = false;   //如果columnguolvsql中包含order 则sfpaixu=true
			if(null != filterMap.get("columnguolvsql") &&! "".equals(filterMap.get("columnguolvsql"))){
				String sqlValue =  filterMap.get("columnguolvsql").toString();
				if(sqlValue.startsWith("order")){
					sfpaixu = true;
				}else{
					queryString.append( filterMap.get("columnguolvsql") );
					queryCountString.append( filterMap.get("columnguolvsql"));
				}
				
			}
			String sidx = (String) filterMap.get("sidx");
			String sord = (String) filterMap.get("sord");
			if(sfpaixu){
				queryString.append(" " + filterMap.get("columnguolvsql") + " ");
			}else if(null != filterMap.get("isOrderBy") &&! "".equals(filterMap.get("isOrderBy"))){
				queryString.append("order by "+filterMap.get("isOrderBy")+" ");
			}else{
				queryString.append(" order by " + sidx + " " + sord + " ");
			}
		}
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}
	public PageResult queryAutoFormData(Map filterMap) {
		String tableName = filterMap.get("tableName").toString();
		StringBuffer queryColumn = new StringBuffer();
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();
		String hql = "from AutoFormsField a where a.formsId="+filterMap.get("formId").toString() +" order by  showSeq,id asc ";
		List<AutoFormsField> list = getHibernateTemplate().find(hql);
		String cloumn = "";
		String leftSql = "";
		String type = "";
		int j = 1;
		for(int i = 0 ; i < list.size(); i++){
			AutoFormsField aff = list.get(i);
			if(!"ID".equals(aff.getFieldName())&&!"".equals(aff.getPageDisplayType())&&null!=aff.getPageDisplayType()){
				type =  aff.getPageDisplayType().toString();
			}
			if("11".equals(type)){//字段是选择人员
				String bm = "t"+j;
				queryColumn.append(bm+".username as "+aff.getFieldName()+",");
	//			queryColumn.append("t."+aff.getFieldName()+",");
				leftSql += " left join sys_users "+bm+" on "+bm+".userid in (t."+aff.getFieldName()+")";
				j++;
			}else if("10".equals(type)){//字段是部门
				String bm = "t"+j;
				queryColumn.append(bm+".org_name as "+aff.getFieldName()+",");
				leftSql += " left join sys_organization "+bm+" on t."+aff.getFieldName()+" = "+bm+".org_id";
				j++;
			}else if("13".equals(type)){//字段是上传文件
				String bm = "t"+j;
				queryColumn.append(bm+".FILE_NAME as "+aff.getFieldName()+",");
				leftSql += " left join tdm_annex "+bm+" on t."+aff.getFieldName()+" = "+bm+".id";
				j++;
			}
			else if(!"".equals(aff.getForeignTableName()) && aff.getForeignTableName() != null){
				if(aff.getForeignDisCloumn().contains(",")){
					String bm = "t"+j;
					queryColumn.append(bm+"."+aff.getForeignDisCloumn().substring(0, aff.getForeignDisCloumn().indexOf(","))+" as "+aff.getFieldName()+",");
					leftSql += " left join "+aff.getForeignTableName()+" "+bm+" on t."+aff.getFieldName()+"||'' = "+bm+"."+aff.getForeignCloumn()+"||''";
					j++;
				}else{
					
					String bm = "t"+j;
					queryColumn.append(bm+"."+aff.getForeignDisCloumn()+" as "+aff.getFieldName()+",");
					leftSql += " left join "+aff.getForeignTableName()+" "+bm+" on t."+aff.getFieldName()+"||'' = "+bm+"."+aff.getForeignCloumn()+"||''";
					j++;
				}
			}else{
				queryColumn.append("t."+aff.getFieldName()+",");
			}
		}
		
		if(queryColumn.toString().length()>0){
			cloumn = queryColumn.toString().substring(0,queryColumn.toString().length()-1);
		}
		
		queryCountString.append(" select count(*) from  "+ tableName +" t " + leftSql );
		queryString.append(" select " + cloumn +" from "+ tableName +" t " + leftSql );
		
		queryCountString.append(" where 1=1 ");
		queryString.append(" where 1=1 ");
		if (filterMap!= null && !filterMap.isEmpty()) {
			
			if(null != filterMap.get("type") &&! "".equals(filterMap.get("type"))){
				queryString.append("and CATEGORY = '" + filterMap.get("type") + "'  ");
				queryCountString.append("and CATEGORY = '" + filterMap.get("type") + "'  ");
			}
			if(null != filterMap.get("formid") &&! "".equals(filterMap.get("formid"))){
				queryString.append("and auto_froms_id = '" + filterMap.get("formid") + "'  ");
				queryCountString.append("and auto_froms_id = '" + filterMap.get("formid") + "'  ");
			}
			if(null != filterMap.get("queryColumn") && !"".equals(filterMap.get("queryColumn"))){
				String queryColumns = String.valueOf(filterMap.get("queryColumn"));
				for(String column : queryColumns.split(",")){
					if(null != filterMap.get(column) &&! "".equals(filterMap.get(column))){
						String col = StringUtil.encodingUrl(filterMap.get(column).toString()).trim();
						if (col.indexOf(",")>0){
							String[] arrayValues = col.split(",");
							queryString.append(" and ( ");
							queryCountString.append(" and ( ");
							for (int i=0;i<arrayValues.length;i++){
								if (i==0){
									queryString.append(" t."+column+" like '%" + arrayValues[i] + "%' ");
									queryCountString.append(" t."+column+" like '%" + arrayValues[i] + "%' ");
								}
								else{
									queryString.append(" or t."+column+" like '%" + arrayValues[i] + "%' ");
									queryCountString.append(" or t."+column+" like '%" + arrayValues[i] + "%' ");
								}
							}
							queryString.append(" ) ");
							queryCountString.append(" ) ");
						}
						else{
							//查询数据类型			
							String hqlType="select fieldType from AutoFormsField t where t.fieldName='"+column+"'";
							List<String> strTypeL=getSession().createQuery(hqlType).list();
							String strType=strTypeL.get(0).toString();
							//
							if(strType.equals("DATE"))
							{
								queryString.append("and t."+column+"=to_date('" + col + "','YYYY/MM/DD')");
								queryCountString.append("and t."+column+"=to_date('" + col + "','YYYY/MM/DD')");
							
							}
							else
							{
								queryString.append("and t."+column+" like '%" + col + "%' ");
								queryCountString.append("and t."+column+" like '%" + col + "%' ");
							}	
						}		
					}
				}
			}
			if(null != filterMap.get("byTableColumn") &&! "".equals(filterMap.get("byTableColumn"))){
				queryString.append("and "+filterMap.get("byTableColumn")+" = '"+filterMap.get("byTableValue")+"' ");
				queryCountString.append("and "+filterMap.get("byTableColumn")+" = '"+filterMap.get("byTableValue")+"' ");
			}
			boolean sfpaixu = false;   //如果columnguolvsql中包含order 则sfpaixu=true
			if(null != filterMap.get("columnguolvsql") &&! "".equals(filterMap.get("columnguolvsql"))){
				String sqlValue =  filterMap.get("columnguolvsql").toString();
				if(sqlValue.startsWith("order")){
					sfpaixu = true;
				}else{
					queryString.append( filterMap.get("columnguolvsql") );
					queryCountString.append( filterMap.get("columnguolvsql"));
				}
				
			}
			String sidx = (String) filterMap.get("sidx");
			String sord = (String) filterMap.get("sord");
			if(sfpaixu){
				queryString.append(" " + filterMap.get("columnguolvsql") + " ");
			}else if(null != filterMap.get("isOrderBy") &&! "".equals(filterMap.get("isOrderBy"))){
				queryString.append("order by "+filterMap.get("isOrderBy")+" ");
			}else{
				queryString.append(" order by " + sidx + " " + sord + " ");
			}
		}
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}
	public String[] SplitNos(String pipesectionnos){
		String[] pipesectionnoArr=pipesectionnos.split(",");
		int sectionNum=pipesectionnoArr.length;
		int groupNum=sectionNum/1000+1;
		String[]  subsql = new String[groupNum];
		for(int i=0;i<groupNum;i++)
		{
			subsql[i]="";
			for(int k=0;k<1000;k++)
			{
				int index=i*1000+k;
				if(index>=sectionNum)
				{
					break;
				}
				subsql[i]+="'";
				subsql[i]+=pipesectionnoArr[index];
				subsql[i]+="'";
				subsql[i]+=",";
			}
			subsql[i]=subsql[i].substring(0,subsql[i].length()-1);
		}
		return subsql;
	}
	public PageResult queryAutoFormDataQuery(Map filterMap) {
		String tableName = filterMap.get("tableName").toString();
		StringBuffer queryColumn = new StringBuffer();
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();
		String hql = "from AutoFormsField a where a.formsId="+filterMap.get("formId").toString() +" order by  showSeq,id asc ";
		List<AutoFormsField> list = getHibernateTemplate().find(hql);
		String cloumn = "";
		String leftSql = "";
		String type = "";
		int j = 1;
		for(int i = 0 ; i < list.size(); i++){
			AutoFormsField aff = list.get(i);
			if(!"ID".equals(aff.getFieldName())&&!"".equals(aff.getPageDisplayType())&&null!=aff.getPageDisplayType()){
				type =  aff.getPageDisplayType().toString();
			}
			if("11".equals(type)){//字段是选择人员
				String bm = "t"+j;
				queryColumn.append(bm+".username as "+aff.getFieldName()+",");
	//			queryColumn.append("t."+aff.getFieldName()+",");
				leftSql += " left join sys_users "+bm+" on "+bm+".userid in (t."+aff.getFieldName()+")";
				j++;
			}else if("10".equals(type)){//字段是部门
				String bm = "t"+j;
				queryColumn.append(bm+".org_name as "+aff.getFieldName()+",");
				leftSql += " left join sys_organization "+bm+" on t."+aff.getFieldName()+" = "+bm+".org_id";
				j++;
			}else if("13".equals(type)){//字段是上传文件
				String bm = "t"+j;
				queryColumn.append(bm+".FILE_NAME as "+aff.getFieldName()+",");
				leftSql += " left join tdm_annex "+bm+" on t."+aff.getFieldName()+" = "+bm+".id";
				j++;
			}
			else if(!"".equals(aff.getForeignTableName()) && aff.getForeignTableName() != null){
//				if(aff.getForeignDisCloumn().contains(",")){
//					String bm = "t"+j;
//					queryColumn.append(bm+"."+aff.getForeignDisCloumn().substring(0, aff.getForeignDisCloumn().indexOf(","))+" as "+aff.getFieldName()+",");
//					leftSql += " left join "+aff.getForeignTableName()+" "+bm+" on t."+aff.getFieldName()+"||'' = "+bm+"."+aff.getForeignCloumn()+"||''";
//					j++;
//				}else{
//					
//					String bm = "t"+j;
//					queryColumn.append(bm+"."+aff.getForeignDisCloumn()+" as "+aff.getFieldName()+",");
//					leftSql += " left join "+aff.getForeignTableName()+" "+bm+" on t."+aff.getFieldName()+"||'' = "+bm+"."+aff.getForeignCloumn()+"||''";
//					j++;
//				}
				queryColumn.append("t."+aff.getFieldName()+",");
			}else{
				queryColumn.append("t."+aff.getFieldName()+",");
			}
		}
		
		if(queryColumn.toString().length()>0){
			cloumn = queryColumn.toString().substring(0,queryColumn.toString().length()-1);
		}
		
		queryCountString.append(" select count(*) from  "+ tableName +" t " + leftSql );
		queryString.append(" select " + cloumn +" from "+ tableName +" t " + leftSql );
		
		queryCountString.append(" where 1=1 ");
		queryString.append(" where 1=1 ");
		
		if (filterMap!= null && !filterMap.isEmpty()) {
			if(null != filterMap.get("isXLJC") &&"true".equals(filterMap.get("isXLJC"))){
				queryString.append("and testresult = '疑似泄漏'");
				queryCountString.append("and testresult = '疑似泄漏'");
			}
			
			String pipesectionnos=null;
			Object obj=filterMap.get("pipesectionnos");
			if(obj!=null)
			{
				pipesectionnos=obj.toString();				
			}
			
			if(pipesectionnos!=null&&"".equals(pipesectionnos))
			{
				queryString.append("and pipesectionno in ('')");
				queryCountString.append("and pipesectionno in ('')");				
			
			}
			else if(null != pipesectionnos &&! "".equals(pipesectionnos)){
				String subsql[]=SplitNos(pipesectionnos);
				int groupNum=subsql.length;				
				queryString.append("and (");
				queryCountString.append("and (");
				for(int i=0;i<groupNum-1;i++)
				{
					queryString.append("pipesectionno in (" + subsql[i] + ") or ");
					queryCountString.append("pipesectionno in (" + subsql[i] + ") or ");				
				}
				queryString.append("pipesectionno in (" + subsql[groupNum-1] + ") ");
				queryCountString.append("pipesectionno in (" + subsql[groupNum-1] + ")  ");	
				queryString.append(")");
				queryCountString.append(")");
				//queryString.append("and pipesectionno in (" + filterMap.get("pipesectionnos") + ") ");
				//queryCountString.append("and pipesectionno in (" + filterMap.get("pipesectionnos") + ")  ");
			}
			
			String tasknos=null;
			obj=filterMap.get("tasknos");
			if(obj!=null)
			{
				tasknos=obj.toString();
			}
			
			if(tasknos!=null&&"".equals(tasknos))
			{
				queryString.append("and taskno in ('')");
				queryCountString.append("and taskno in ('')");				
			
			}
			else if(null != tasknos &&! "".equals(tasknos)){
				String subsql[]=SplitNos(tasknos);
				int groupNum=subsql.length;				
				queryString.append("and (");
				queryCountString.append("and (");
				for(int i=0;i<groupNum-1;i++)
				{
					queryString.append("taskno in (" + subsql[i] + ") or ");
					queryCountString.append("taskno in (" + subsql[i] + ") or ");				
				}
				queryString.append("taskno in (" + subsql[groupNum-1] + ") ");
				queryCountString.append("taskno in (" + subsql[groupNum-1] + ")  ");	
				queryString.append(")");
				queryCountString.append(")");
				//queryString.append("and pipesectionno in (" + filterMap.get("pipesectionnos") + ") ");
				//queryCountString.append("and pipesectionno in (" + filterMap.get("pipesectionnos") + ")  ");
			}
			
			if(null != filterMap.get("pipelineno") &&! "".equals(filterMap.get("pipelineno"))){
				queryString.append("and pipelineno = '" + filterMap.get("pipelineno") + "'  ");
				queryCountString.append("and pipelineno = '" + filterMap.get("pipelineno") + "'  ");
			}
			if(null != filterMap.get("pipenetno") &&! "".equals(filterMap.get("pipenetno"))){
				queryString.append("and pipenetno = '" + filterMap.get("pipenetno") + "'  ");
				queryCountString.append("and pipenetno = '" + filterMap.get("pipenetno") + "'  ");
			}
			if(null != filterMap.get("type") &&! "".equals(filterMap.get("type"))){
				queryString.append("and CATEGORY = '" + filterMap.get("type") + "'  ");
				queryCountString.append("and CATEGORY = '" + filterMap.get("type") + "'  ");
			}
			if(null != filterMap.get("formid") &&! "".equals(filterMap.get("formid"))){
				queryString.append("and auto_froms_id = '" + filterMap.get("formid") + "'  ");
				queryCountString.append("and auto_froms_id = '" + filterMap.get("formid") + "'  ");
			}
			if(null != filterMap.get("queryColumn") && !"".equals(filterMap.get("queryColumn"))){
				String queryColumns = String.valueOf(filterMap.get("queryColumn"));
				for(String column : queryColumns.split(",")){
					if(null != filterMap.get(column) &&! "".equals(filterMap.get(column))){
						String col = StringUtil.encodingUrl(filterMap.get(column).toString()).trim();
						if (col.indexOf(",")>0){
							String[] arrayValues = col.split(",");
							queryString.append(" and ( ");
							queryCountString.append(" and ( ");
							for (int i=0;i<arrayValues.length;i++){
								if (i==0){
									queryString.append(" t."+column+" like '%" + arrayValues[i] + "%' ");
									queryCountString.append(" t."+column+" like '%" + arrayValues[i] + "%' ");
								}
								else{
									queryString.append(" or t."+column+" like '%" + arrayValues[i] + "%' ");
									queryCountString.append(" or t."+column+" like '%" + arrayValues[i] + "%' ");
								}
							}
							queryString.append(" ) ");
							queryCountString.append(" ) ");
						}
						else{
							//查询数据类型			
							String hqlType="select fieldType from AutoFormsField t where t.fieldName='"+column+"'";
							List<String> strTypeL=getSession().createQuery(hqlType).list();
							String strType=strTypeL.get(0).toString();
							//
							if(strType.equals("DATE"))
							{
								queryString.append("and t."+column+"=to_date('" + col + "','YYYY/MM/DD')");
								queryCountString.append("and t."+column+"=to_date('" + col + "','YYYY/MM/DD')");
							
							}
							else
							{
								queryString.append("and t."+column+" like '%" + col + "%' ");
								queryCountString.append("and t."+column+" like '%" + col + "%' ");
							}	
						}		
					}
				}
			}
			if(null != filterMap.get("byTableColumn") &&! "".equals(filterMap.get("byTableColumn"))){
				queryString.append("and "+filterMap.get("byTableColumn")+" = '"+filterMap.get("byTableValue")+"' ");
				queryCountString.append("and "+filterMap.get("byTableColumn")+" = '"+filterMap.get("byTableValue")+"' ");
			}
			boolean sfpaixu = false;   //如果columnguolvsql中包含order 则sfpaixu=true
			if(null != filterMap.get("columnguolvsql") &&! "".equals(filterMap.get("columnguolvsql"))){
				String sqlValue =  filterMap.get("columnguolvsql").toString();
				if(sqlValue.startsWith("order")){
					sfpaixu = true;
				}else{
					queryString.append( filterMap.get("columnguolvsql") );
					queryCountString.append( filterMap.get("columnguolvsql"));
				}
				
			}
			String sidx = (String) filterMap.get("sidx");
			String sord = (String) filterMap.get("sord");
			if(sfpaixu){
				queryString.append(" " + filterMap.get("columnguolvsql") + " ");
			}else if(null != filterMap.get("isOrderBy") &&! "".equals(filterMap.get("isOrderBy"))){
				queryString.append("order by "+filterMap.get("isOrderBy")+" ");
			}else{
				queryString.append(" order by " + sidx + " " + sord + " ");
			}
		}
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}
	
	public PageResult queryAutoAnalysisFormData(Map filterMap) {
		String tableName = filterMap.get("tableName").toString();
		StringBuffer queryColumn = new StringBuffer();
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();
		String hql = "from AutoFormsField a where a.formsId="+filterMap.get("formId").toString() +" order by  showSeq,id asc ";
		List<AutoFormsField> list = getHibernateTemplate().find(hql);
		String cloumn = "";
		queryColumn.append("t.ID,t.flowtaskid,t.filemodeid,jy.renwubh,jy.shiyanlx,jy.jieduanlx,jy.shiyankssj");
		String leftSql = "";
		String type = "";
		int j = 1;
		for(int i = 0 ; i < list.size(); i++){
			AutoFormsField aff = list.get(i);
			if ("ID".equals(aff.getFieldName())||"FLOWTASKID".equals(aff.getFieldName())||"FILEMODEID".equals(aff.getFieldName())){
				continue;
			}
			if("11".equals(type)){//字段是选择人员
				String bm = "t"+j;
				queryColumn.append(","+bm+".username as "+aff.getFieldName());
				leftSql += " left join sys_users "+bm+" on "+bm+".userid in (t."+aff.getFieldName()+")";
				j++;
			}else if("10".equals(type)){//字段是部门
				String bm = "t"+j;
				queryColumn.append(","+bm+".org_name as "+aff.getFieldName());
				leftSql += " left join sys_organization "+bm+" on t."+aff.getFieldName()+" = "+bm+".org_id";
				j++;
			}else if("13".equals(type)){//字段是上传文件
				String bm = "t"+j;
				queryColumn.append(","+bm+".FILE_NAME as "+aff.getFieldName());
				leftSql += " left join tdm_annex "+bm+" on t."+aff.getFieldName()+" = "+bm+".id";
				j++;
			}
			else if(!"".equals(aff.getForeignTableName()) && aff.getForeignTableName() != null){
				if(aff.getForeignDisCloumn().contains(",")){
					String bm = "t"+j;
					queryColumn.append(","+bm+"."+aff.getForeignDisCloumn().substring(0, aff.getForeignDisCloumn().indexOf(","))+" as "+aff.getFieldName());
					leftSql += " left join "+aff.getForeignTableName()+" "+bm+" on t."+aff.getFieldName()+"||'' = "+bm+"."+aff.getForeignCloumn()+"||''";
					j++;
				}else{
					
					String bm = "t"+j;
					queryColumn.append(","+bm+"."+aff.getForeignDisCloumn()+" as "+aff.getFieldName());
					leftSql += " left join "+aff.getForeignTableName()+" "+bm+" on t."+aff.getFieldName()+"||'' = "+bm+"."+aff.getForeignCloumn()+"||''";
					j++;
				}
			}else{
				queryColumn.append(","+"t."+aff.getFieldName());
			}
		}
		cloumn = queryColumn.toString();

		leftSql+=" left join flow_task ft on t.flowtaskid=ft.id  left join kl_junyans jy on jy.id=ft.main_id ";
		queryCountString.append(" select count(*) from  "+ tableName +" t " + leftSql );
		queryString.append(" select " + cloumn +" from "+ tableName +" t " + leftSql );
		
		queryCountString.append(" where 1=1 ");
		queryString.append(" where 1=1 ");
		if (filterMap!= null && !filterMap.isEmpty()) {
			if(null != filterMap.get("formid") &&! "".equals(filterMap.get("formid"))){
				queryString.append("and auto_froms_id = '" + filterMap.get("formid") + "'  ");
				queryCountString.append("and auto_froms_id = '" + filterMap.get("formid") + "'  ");
			}
			if(null != filterMap.get("queryColumn") && !"".equals(filterMap.get("queryColumn"))){
				String queryColumns = String.valueOf(filterMap.get("queryColumn"));
				for(String column : queryColumns.split(",")){
					if(null != filterMap.get(column) &&! "".equals(filterMap.get(column))){
						String col = StringUtil.encodingUrl(filterMap.get(column).toString()).trim();
						queryString.append("and t."+column+" like '%" + col + "%' ");
						queryCountString.append("and t."+column+" like '%" + col + "%' ");
					}
				}
			}
			if(null != filterMap.get("byTableColumn") &&! "".equals(filterMap.get("byTableColumn"))){
				queryString.append("and "+filterMap.get("byTableColumn")+" = '"+filterMap.get("byTableValue")+"' ");
				queryCountString.append("and "+filterMap.get("byTableColumn")+" = '"+filterMap.get("byTableValue")+"' ");
			}
			boolean sfpaixu = false;   //如果columnguolvsql中包含order 则sfpaixu=true
			if(null != filterMap.get("columnguolvsql") &&! "".equals(filterMap.get("columnguolvsql"))){
				String sqlValue =  filterMap.get("columnguolvsql").toString();
				if(sqlValue.startsWith("order")){
					sfpaixu = true;
				}else{
					queryString.append( filterMap.get("columnguolvsql") );
					queryCountString.append( filterMap.get("columnguolvsql"));
				}
				
			}
			String sidx = (String) filterMap.get("sidx");
			String sord = (String) filterMap.get("sord");
			if(sfpaixu){
				queryString.append(" " + filterMap.get("columnguolvsql") + " ");
			}else if(null != filterMap.get("isOrderBy") &&! "".equals(filterMap.get("isOrderBy"))){
				queryString.append("order by "+filterMap.get("isOrderBy")+" ");
			}else{
				queryString.append(" order by " + sidx + " " + sord + " ");
			}
		}
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}

	
	public PageResult queryAutoPageFormData(Map filterMap) {
		String columnSql = filterMap.get("columnSql").toString();
		String countsql = columnSql.substring(columnSql.toLowerCase().indexOf("from"));
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();
		//queryCountString.append(" select count(*) "+countsql);
		queryCountString.append(" select count(*) from ("+columnSql+") ");
		queryString.append(columnSql);
			
		if (filterMap!= null && !filterMap.isEmpty()) {
			 
			String sidx = (String) filterMap.get("sidx");
			String sord = (String) filterMap.get("sord");
			//queryString.append(" order by " + sidx + " " + sord + " ");
		}
		//System.out.println("-------"+queryString.toString());
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}
	
	public String executeSql(String sql) {
		String id = "";
		Query query = getSession().createSQLQuery(sql);
		int count = query.executeUpdate();
		if(count>0){
			id = count+"";
		}
		return id;
	}
	
	public void executeSqlmore(List<String> lists){
		try {
			Connection con = getSession().connection();
			con.setAutoCommit(false);
			Statement stmt = con.createStatement(); 
			for(int i=0;i<lists.size();i++){
				stmt.addBatch(lists.get(i)); 
			}
			stmt.executeBatch(); 
			stmt.close(); 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public String executeId(String sql) {
		String id = "";
		Query query = getSession().createSQLQuery(sql);
		List list = query.list();
		getSession().clear();
		return String.valueOf(list.get(0));
	}
	
	public List getDatabySql(String sql) {
		Query query = getSession().createSQLQuery(sql);
		List l =query.list(); 
		return l;
	}

	
	public PageResult queryChooseFormData(Map filterMap) {
		String tableName = filterMap.get("tableName").toString();
		String disname = filterMap.get("disname").toString();
		String disnameone=disname;
		//选择工艺的处理
		String columns = filterMap.get("columns").toString();
		if(!"".equals(tableName)&&"DIC_STANDARD".equals(tableName)){
			disname =columns ;
		}else{
			if(tableName.equals("SYS_ORGANIZATION"))
			{
				disname = " org_id,"+disname;
			}
			else if(tableName.equals("SYS_USERS"))
			{
				disname = " userid,"+disname;
			}
			else
			{
				disname = " id,"+disname;
			}
			
		}
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();
		//queryCountString.append(" select count(*) from "+tableName+" where 1=1 ");
		//queryString.append("SELECT distinct "+disname+" FROM "+tableName+" t where 1=1 ");
		
		queryCountString.append(" select count(distinct "+disnameone+") from "+tableName+" where 1=1 ");
		if(tableName.equals("PTEM_TYPE_PRESSURELEVEL"))
		{
			queryString.append("select id,typename from ptem_type_pressurelevel t where id in (select min(id) from ptem_type_pressurelevel group by typename)");			
		}
		else{
			queryString.append("SELECT "+disname+" FROM "+tableName+" t where 1=1 ");
		}
		
		
				
		if (filterMap!= null && !filterMap.isEmpty()) {
			if(tableName.equals("SYS_USERS")){
				if(null != filterMap.get("orgid") &&! "".equals(filterMap.get("orgid"))){
					String org_id=filterMap.get("orgid").toString();
					queryCountString.append(" and org_id= '"+org_id+"'");
					queryString.append(" and org_id= '"+org_id+"'");
				}
			}
			//String unittype1=filterMap.get("unittype").toString();
			if(tableName.equals("SYS_ORGANIZATION"))
			{
				if(null != filterMap.get("unittype") &&! "".equals(filterMap.get("unittype"))){
					String unittype=filterMap.get("unittype").toString();
					queryCountString.append(" and unit_type ='"+unittype+"'");		
					queryString.append(" and unit_type ='"+unittype+"'");		
				}
			}
			if(tableName.equals("PTEM_TYPE_UNITAPTITUDE"))//单位资质
			{
				if(null != filterMap.get("unittype") &&! "".equals(filterMap.get("unittype"))){
					String unittype=filterMap.get("unittype").toString();
					queryCountString.append(" and APTITUDETYPE ='"+unittype+"'");		
					queryString.append(" and APTITUDETYPE ='"+unittype+"'");		
				}
			}
			if(tableName.equals("PTEM_TYPE_UNITLEVEL"))//资质等级
			{
				if(null != filterMap.get("unittype") &&! "".equals(filterMap.get("unittype"))){
					String unittype=filterMap.get("unittype").toString();
					queryCountString.append(" and LEVELTYPE ='"+unittype+"'");		
					queryString.append(" and LEVELTYPE ='"+unittype+"'");		
				}
			}
			if(tableName.equals("PTEM_TYPE_UNITSTANDARD"))//规范
			{
				if(null != filterMap.get("unittype") &&! "".equals(filterMap.get("unittype"))){
					String unittype=filterMap.get("unittype").toString();
					queryCountString.append(" and STANDARDTYPE ='"+unittype+"'");		
					queryString.append(" and STANDARDTYPE ='"+unittype+"'");		
				}
			}
			if(tableName.equals("PTEM_TYPE_PRESSURELEVEL")&&null != filterMap.get("media")&&"" != filterMap.get("media"))
			{
				String media=filterMap.get("media").toString();
				queryCountString.append(" and media ='"+media+"'");		
				queryString.append(" and media ='"+media+"'");				
			}
			
			if(null != filterMap.get("fitterCondition") &&! "".equals(filterMap.get("fitterCondition"))){
				queryString.append(filterMap.get("fitterCondition"));
				queryCountString.append(filterMap.get("fitterCondition"));
			}
			//if(null != filterMap.get("formid") &&! "".equals(filterMap.get("formid"))){
			//	queryString.append("and auto_froms_id = '" + filterMap.get("formid") + "'  ");
			//	queryCountString.append("and auto_froms_id = '" + filterMap.get("formid") + "'  ");
			//}
			if(null != filterMap.get("queryColumn") && !"".equals(filterMap.get("queryColumn"))){
				String[] allcolumn = filterMap.get("queryColumn").toString().split(",");
				for(int i=0;i<allcolumn.length;i++){
					if(null != filterMap.get(allcolumn[i]) && !"".equals(filterMap.get(allcolumn[i]))){
						queryString.append("and "+allcolumn[i]+" like '%" + filterMap.get(allcolumn[i])  + "%' ");
						queryCountString.append("and "+allcolumn[i]+" like '%" +  filterMap.get(allcolumn[i]) + "%' ");
				
					}
				}
		     }
			if(null != filterMap.get("tiaojian") && !"".equals(filterMap.get("tiaojian"))){
				queryString.append( filterMap.get("tiaojian") );
				queryCountString.append( filterMap.get("tiaojian") );
			}
			String sidx = (String) filterMap.get("sidx");
			String sord = (String) filterMap.get("sord");
			if(tableName.equals("SYS_ORGANIZATION"))
			{
				queryString.append("order by t.org_ID desc ");
			}
			else if(tableName.equals("SYS_USERS"))
			{
				queryString.append("order by t.userid desc ");
			}
			else
			{
				queryString.append("order by t.ID desc ");
			}
			
		}
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}
	
	
	
//	public String saveDataManage(ZkhDataManage data) {
//		return this.getHibernateTemplate().save(data).toString();
//	}
//	
//	public ZkhDataManage queryData(String id) {
//		ZkhDataManage data = (ZkhDataManage)this.getHibernateTemplate().get(ZkhDataManage.class, Long.parseLong(id));
//		return data;
//	}
//	public void updateDataManage(ZkhDataManage data) {
//		this.getHibernateTemplate().update(data);
//	}
//	public void deleteDataManage(String ids) {
//		ZkhDataManage data = (ZkhDataManage)this.getHibernateTemplate().get(ZkhDataManage.class, Long.parseLong(ids));
//		this.getHibernateTemplate().delete(data);
//	}

	
	public Flowtype findFlowType(String flowid) {
		Flowtype data = (Flowtype)this.getHibernateTemplate().get(Flowtype.class, Long.parseLong(flowid));
		return data;
	}

	
	public List findProject(String flowid, String taskId) {
		// TODO Auto-generated method stub
		return null;
	}

	
	public TdmCustomNo goUpdateCustomNumber(String id) {
		TdmCustomNo data = (TdmCustomNo)this.getHibernateTemplate().get(TdmCustomNo.class, Long.parseLong(id));
		return data;
	}
	
	public String saveObject(Object obj){
		return this.getHibernateTemplate().save(obj).toString();
	}
	
	public String importGuanduan(XSSFSheet  hssfSheet,LogInfo loginfo){	
		boolean isError=false;
		String insertSql = "insert into PTEM_PIPESECTION(ID,PIPESECTIONNO,RUNPRESSURELEVEL," +
		"BEGINNODEY,BEGINNODEX,BEGINNODEH,BEGINNODECODE,BEGINNODENAME,ENDNODEY," +
		"ENDNODEX,ENDNODEH,ENDNODECODE,ENDNODENAME,BEGINNODEL,BEGINNODEB," +
		"BEGINNODEELLIPSOIDH,ENDNODEL,ENDNODEB,ENDNODEELLIPSOIDH,PIPELINENO," +
		"PRESSURELEVEL,AERATEDATE,PAVEMODE,MATERIAL,MATERIALMARK,DIAMETER," +
		"OUTERDIAMETER,THICKNESS,PLANELENGTH,REALLENGTH,MANAGEUNIT,MANAGEDEPARTMENT," +
		"JUDGEREPEAT,REMARK,SIGNEDUNIT,SIGNED)" +
		"values(SEQ_PTEM_PIPESECTION.nextval,?,?,?,?,?,?,?,?,?,?," +
		"?,?,?,?,?,?,?,?,?,?," +
		"?,?,?,?,?,?,?,?,?,?,?," +
		"?,?,?,?)";

		Session session = getHibernateTemplate().getSessionFactory().openSession();
		Connection conn = session.connection();
		if (hssfSheet==null)
		{
			return "文件有误";
		}
		try {
			PreparedStatement stmt = conn.prepareStatement(insertSql);
			conn.setAutoCommit(true);			
			int successrownum=0;
			int failrownum=0;
			int rowSize=hssfSheet.getLastRowNum();
			for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				XSSFRow  hssfRow = hssfSheet.getRow(rowNum);
				if (hssfRow != null) {
					//读取数据
					for(int j=0;j<35;j++){
						String cellValue="";							
						cellValue = getCellValue(hssfRow.getCell(j));
						//起点经度、起点纬度、终点经度、终点纬度去掉E、N
						if(j==12||j==13||j==15||j==16)
						{
							if(cellValue!=null&&!cellValue.equals(""))
							{
								cellValue = cellValue.replace("E", "").replace("N", "");
							}
							
						}
						if (cellValue!=null){							
							stmt.setString(j+1, cellValue);
						}
						else{
							stmt.setString(j+1, "");
						}
					}
				}
				try{
					stmt.execute();	
					successrownum++;
				}catch(Exception e)
				{
					String msg=e.getMessage();
					failrownum++;
					isError=true;
					msg="第"+(rowNum+1)+"行导入出错："+msg;
					//System.out.println(rowNum);
					//System.out.println(stmt.toString());
					//System.out.println(msg);
					try {				
						LogInfo log=new LogInfo();
						log.setLogCreatTime(new Timestamp(System.currentTimeMillis()));
						log.setLogLoginIp(loginfo.getLogLoginIp());
						log.setLogLoginTime(loginfo.getLogLoginTime());
						log.setLogOrgId(loginfo.getLogOrgId());
						log.setLogOrgName(loginfo.getLogOrgName());
						log.setLogUserId(loginfo.getLogUserId());
						log.setLogUserName(loginfo.getLogUserName());
						logService.saveLogInfo(log, "表C 管段","导入文件",msg,4);
					} catch (ServiceException e1) {
						// TODO Auto-generated catch block
						e1.printStackTrace();
					}
					continue;
				}					
			}
			session.close();			
	 		if(successrownum==0&&failrownum==0)
	 		{
	 			String msg="没有数据！";
	 			return msg;	
	 		}
	 		if(isError==true)
	 		{
	 			String msg=successrownum+"行导入成功，"+failrownum+"行导入失败。失败原因请查看日志！";
	 			return msg;	
	 		}					
			else
			{
				return "success";
			}		
	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return e.getMessage();
		}	
	}
//	public void importGuanduan(XSSFSheet  hssfSheet){
//		String insertSql = "insert into PTEM_PIPESECTION(ID,PIPESECTIONNO,RUNPRESSURELEVEL," +
//		"BEGINNODEY,BEGINNODEX,BEGINNODEH,BEGINNODECODE,BEGINNODENAME,ENDNODEY," +
//		"ENDNODEX,ENDNODEH,ENDNODECODE,ENDNODENAME,BEGINNODEL,BEGINNODEB," +
//		"BEGINNODEELLIPSOIDH,ENDNODEL,ENDNODEB,ENDNODEELLIPSOIDH,PIPELINENO," +
//		"PRESSURELEVEL,AERATEDATE,PAVEMODE,MATERIAL,MATERIALMARK,DIAMETER," +
//		"OUTERDIAMETER,THICKNESS,PLANELENGTH,REALLENGTH,MANAGEUNIT,MANAGEDEPARTMENT," +
//		"JUDGEREPEAT,REMARK,SIGNEDUNIT,SIGNED)" +
//		"values(SEQ_PTEM_PIPESECTION.nextval,?,?,?,?,?,?,?,?,?,?," +
//		"?,?,?,?,?,?,?,?,?,?," +
//		"?,?,?,?,?,?,?,?,?,?,?," +
//		"?,?,?,?)";
//
//		Session session = getHibernateTemplate().getSessionFactory().openSession();
//		Connection conn = session.connection();
//		try {
//			PreparedStatement stmt = conn.prepareStatement(insertSql);
//			conn.setAutoCommit(true);
//			if (hssfSheet!=null){
//				for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
//					XSSFRow  hssfRow = hssfSheet.getRow(rowNum);
//					if (hssfRow != null) {
//						//判断是否重复
////			 			String pipesectionno=getCellValue(hssfRow.getCell(0));
////						String sql="select t.PIPESECTIONNO from PTEM_PIPESECTION t " +
////								"where t.PIPESECTIONNO='"+pipesectionno+"'";
////						List<Object> uniquevalues=getDatabySql(sql);
////						if(uniquevalues.size()!=0)
////						{
////							continue;
////						}
//						//读取数据
//						for(int j=0;j<35;j++){
//							String cellValue="";							
//							cellValue = getCellValue(hssfRow.getCell(j));
//							//起点经度、起点纬度、终点经度、终点纬度去掉E、N
//							if(j==12||j==13||j==15||j==16)
//							{
//								cellValue = cellValue.replace("E", "").replace("N", "");
//							}
//							if (cellValue!=null){							
//								stmt.setString(j+1, cellValue);
//							}
//							else{
//								stmt.setString(j+1, "");
//							}
//						}
//					}
//					try{
//						stmt.execute();						
//					}catch(Exception e)
//					{
//						String msg=e.getMessage();
//						//System.out.println(rowNum);
//						//System.out.println(stmt.toString());
//						System.out.println(msg);
//						//logService.saveLogInfo(this.getLogBase(), "表C 管段","导入文件",msg,4);
//					}
//					
////					if (rowNum%100==0){
////						stmt.executeBatch();
////						conn.commit();
////					}
//				}
////				stmt.executeBatch();
////				conn.commit();
//				session.close();
//			}
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//	}
	
	public String getCellValue(XSSFCell cell) {
	         String value = null;
	         if (cell != null) {
	            switch (cell.getCellType()) {
	            case XSSFCell.CELL_TYPE_FORMULA:
	                 // cell.getCellFormula();
	                 try {
	                     //value = String.valueOf(cell.getNumericCellValue());
	                     value = String.valueOf(cell.getRichStringCellValue());
	                 } catch (IllegalStateException e) {
	                	 value = String.valueOf(cell.getNumericCellValue());
	                    //value = String.valueOf(cell.getRichStringCellValue());
	                 }
	                 break;
	            case XSSFCell.CELL_TYPE_NUMERIC:
	                 value = String.valueOf(cell.getNumericCellValue());	            	
	                 break;
	             case XSSFCell.CELL_TYPE_STRING:
	                 value = String.valueOf(cell.getRichStringCellValue());
	                 break;
	             }
	         }
	         if(value!=null&&value.equals("/"))
	         {
	        	 value="";
	         }
	        return value;
	     }
	}
